package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.entity.Student;
import cn.gson.crm.model.po.JobFollowPo;
import cn.gson.crm.model.po.StudentPayPo;
import cn.gson.crm.model.entity.StudentDetail;

/**
 * 潜在学员的录入Dao类
 * 
 * @author Anni
 *
 */
public class StudentDao {
	private int pageSize = 3;

	/**
	 * 显示所有状态为正常的学员
	 * 
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public List<Student> showAllNotAllocationStu(String status, String isAllocation) throws SQLException {
		String sql = "select * from student where stu_status = ? and is_allocation = ? order by id desc";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, status, isAllocation);
		List<Student> student = null;
		if (data != null) {
			student = new ArrayList<>();
			for (Map<String, Object> map : data) {
				student.add(buildPotential(map));
			}
		}
		return student;
	}

	/**
	 * 改变学员的交款状态
	 * 
	 * @param po
	 * @return
	 * @throws SQLException
	 */
	public Boolean changePayStatus(StudentPayPo po) throws SQLException {
		String sql = "update student set is_allpay=? where id=?";
		return Jdbc.getInstace().executeUpdate(sql, po.getIs_allpay(), po.getId());
	}

	/**
	 * 缴费之后给学员加班级类型
	 * 
	 * @param po
	 * @return
	 * @throws SQLException
	 */
	public Boolean addClassType(StudentPayPo po) throws SQLException {
		String sql = "update student set class_type=? where id=?";
		return Jdbc.getInstace().executeUpdate(sql, po.getClassType(), po.getId());
	}

	public List<Student> showAllNotAllocationStu1(String status, String isAllocation) throws SQLException {
		String sql = "select * from student where stu_status = ? and is_allocation = ?";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, status, isAllocation);
		List<Student> student = null;
		if (data != null) {
			student = new ArrayList<>();
			for (Map<String, Object> map : data) {
				student.add(buildPotential(map));
			}
		}
		return student;
	}

	/**
	 * 显示最新录入的三位学生
	 * 
	 * @param status
	 * @param isAllocation
	 * @return
	 * @throws SQLException
	 */
	public List<Student> displayNewEnterStu(String status, String isAllocation) throws SQLException {
		String sql = "select * from student where stu_status = ? and is_allocation=? order by id desc limit 3";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql, status, isAllocation);
		List<Student> student = null;
		if (data != null) {
			student = new ArrayList<>();
			for (Map<String, Object> map : data) {
				student.add(buildPotential(map));
			}
		}
		return student;
	}

	/**
	 * 通过id删除对应学员,将状态改为删除,已分配的员工不能删除。
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean stuDeleteById(String status, String allocation, int id) throws SQLException {
		String sql = "update student set stu_status = ?, is_allocation = ? where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, status, allocation, id);
	}

	/**
	 * 通过id恢复被删除的学员
	 * 
	 * @param status
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean stuDeleteById(String status, int id) throws SQLException {
		String sql = "update student set stu_status = ? where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, status, id);
	}

	/**
	 * 通过id改变状态为正常且将未分配的改为已分配
	 * 
	 * @param status
	 * @param allocation
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean stuChangeAllocation(String status, String allocation, int id) throws SQLException {
		String sql = "update student set stu_status = ?, is_allocation = ? where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, status, allocation, id);
	}

	/**
	 * 改变学生等级
	 * 
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public Boolean changeStuGrade(String gradeId, String stuId) throws SQLException {
		String sql = "UPDATE student SET grade_id=? WHERE id=?;";
		return Jdbc.getInstace().executeUpdate(sql, gradeId, stuId);
	}

	/**
	 * 签约之后学生默认classType为0
	 * 
	 * @param gradeId
	 * @param stuId
	 * @param classType
	 * @return
	 * @throws SQLException
	 */
	public Boolean changeStuGrade(String gradeId, String stuId, Integer classType ,Integer classId) throws SQLException {
		String sql = "UPDATE student SET grade_id=?, class_type = ?,class_id = ? WHERE id=?;";
		return Jdbc.getInstace().executeUpdate(sql, gradeId,classType,classId, stuId);
	}

	/**
	 * 获取不同等级的学生 分页和查询
	 * 
	 * @param page
	 * @param gradeId
	 * @param stuName
	 * @param phone
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getStudentList(Integer page, String gradeId, String stuName, String phone)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * FROM student where grade_id=? and stu_status='正常' ";
		List<Object> params = new ArrayList<>();
		params.add(gradeId);
		sql = buildSqlAndParam(sql, params, stuName, phone);
		sql += " order by stu_createtime desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<Student> poList = null;
		if (rows != null) {
			poList = new ArrayList<>();
			for (Map<String, Object> row : rows) {
				poList.add(buildPotential(row));
			}
		}
		return poList;

	}

	/**
	 * 获取不同等级学生查询和非查询时分页总页数
	 * 
	 * @param gradeId
	 * @param stuName
	 * @param phone
	 * @return
	 * @throws SQLException
	 */
	public Integer getTotalPages(String gradeId, String stuName, String phone) throws SQLException {
		String sql = "select count(*)total FROM student where grade_id=? and stu_status='正常' ";
		List<Object> params = new ArrayList<>();
		params.add(gradeId);
		sql = buildSqlAndParam(sql, params, stuName, phone);
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);
	}

	/**
	 * 参数和sql构建
	 * 
	 * @param sql
	 * @param params
	 * @param stuName
	 * @param phone
	 * @return
	 */
	private String buildSqlAndParam(String sql, List<Object> params, String stuName, String phone) {
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ? ";
			params.add("%" + stuName.trim() + "%");
		}
		if (!StringUtils.isNullOrEmpty(phone)) {
			sql += " and stu_phone like ? ";
			params.add("%" + phone.trim() + "%");
		}

		return sql;

	}

	/**
	 * 学生的新建和保存
	 * 
	 * @param s
	 * @return
	 * @throws SQLException
	 */
	public boolean saveStu(Student s) throws SQLException {
		if (s.getId() != null) {
			// 修改
			String sql = "update student " + "set stu_name = ?, stu_gender = ?, stu_source = ?, stu_education = ?,"
					+ " stu_phone = ?, stu_address = ?, stu_idcard = ?, stu_qq = ?, "
					+ "stu_fathername = ?, stu_fathertel = ?, stu_mothername = ?, stu_mothertel = ?" + "where id = ?";
			return Jdbc.getInstace().executeUpdate(sql, s.getName(), s.getGender(), s.getSource(), s.getEducation(),
					s.getPhone(), s.getAddress(), s.getIdCard(), s.getQq(), s.getFatherName(), s.getFatherTel(),
					s.getMotherName(), s.getMotherTel(), s.getId());
		} else {
			// 新建
			String sql = "insert into student(stu_name,stu_gender,stu_source,stu_education,stu_phone,"
					+ "stu_address,stu_idcard,stu_qq,stu_fathername,stu_fathertel,stu_mothername,stu_mothertel,"
					+ "stu_status,is_allocation)" + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			return Jdbc.getInstace().executeUpdate(sql, s.getName(), s.getGender(), s.getSource(), s.getEducation(),
					s.getPhone(), s.getAddress(), s.getIdCard(), s.getQq(), s.getFatherName(), s.getFatherTel(),
					s.getMotherName(), s.getMotherTel(), s.getStatus(), s.getIsAllocation());
		}
	}

	/**
	 * 通过ID获得状态为正常的未分配的学生
	 * 
	 * @param status
	 * @param allocation
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Student getStudentById(String status, String allocation, int id) throws SQLException {
		String sql = "select * from student where stu_status = ? and is_allocation = ? and id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, allocation, id);
		Student stu = null;
		if (data != null) {
			stu = new Student();
			stu = buildPotential(data);
		}
		return stu;
	}

	/**
	 * 通过名字获取学生
	 * 
	 * @param status
	 * @param allocation
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Student getStudentByName(String status, String allocation, String stuName) throws SQLException {
		String sql = "select * from student where stu_status = ? and is_allocation = ? and stu_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, allocation, stuName);
		Student stu = null;
		if (data != null) {
			stu = new Student();
			stu = buildPotential(data);
		}
		return stu;
	}

	/**
	 * 通过学生ID把分配的学生分配咨询师和改变状态为已分配
	 * 
	 * @param isAllocation
	 * @param counId
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean allocationStu(String isAllocation, int counId, int id) throws SQLException {
		String sql = "update student set is_allocation = ?,counselor_id = ? where id = ?";
		return Jdbc.getInstace().executeUpdate(sql, isAllocation, counId, id);
	}

	/**
	 * 获得搜索总页数
	 * 
	 * @param status
	 *            状态
	 * @param allocation
	 *            分配状态
	 * @param name
	 *            学生姓名
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String allocation, String name) throws SQLException {
		String sql = "select count(*) as total from student where stu_status = ? and is_allocation = ?";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, allocation, name);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(data.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 获得搜索总页数（不含分配）
	 * 
	 * @param status
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String name) throws SQLException {
		String sql = "select count(*) as total from student where stu_status = ?";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, name);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(data.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 带条件的分页查询
	 * 
	 * @param status
	 *            状态
	 * @param allocation
	 *            分配
	 * @param page
	 *            当前页数
	 * @param name
	 *            学生姓名
	 * @return 学生集合
	 * @throws SQLException
	 */
	public List<Student> getPageStuByStaAndAll(String status, String allocation, int page, String name)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from student where stu_status = ? and is_allocation = ?";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, allocation, name);
		sql += "order by id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryEmploList(sql, params.toArray());
	}

	/**
	 * 带条件的分页查询(你包含分配)
	 * 
	 * @param status
	 * @param page
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getPageStuByStaAndAll(String status, int page, String name) throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from student where stu_status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, status, name);
		sql += "order by id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryEmploList(sql, params.toArray());
	}

	private List<Student> queryEmploList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Student> stu = null;
		if (listData != null && listData.size() > 0) {
			stu = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				stu.add(buildPotential(data));
			}
		}
		return stu;
	}

	private String buildSqlAndParams(List<Object> params, String sql, String status, String allocation, String name) {
		params.add(status);
		params.add(allocation);
		if (!StringUtils.isNullOrEmpty(name)) {
			sql += "and stu_name like ?";
			params.add("%" + name.trim() + "%");
		}
		return sql;
	}

	private String buildSqlAndParams(List<Object> params, String sql, String status, String name) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(name)) {
			sql += "and stu_name like ?";
			params.add("%" + name.trim() + "%");
		}
		return sql;
	}

	private Student buildPotential(Map<String, Object> map) {
		Student stu = new Student();
		stu.setId(Integer.parseInt(map.get("id").toString()));
		stu.setName((String) map.get("stu_name"));
		stu.setGender((String) map.get("stu_gender"));
		if (map.get("stu_source") != null) {
			stu.setSource((String) map.get("stu_source"));
		}
		stu.setEducation((String) map.get("stu_education"));
		if (map.get("stu_phone") != null) {
			stu.setPhone((String) map.get("stu_phone"));
		}
		stu.setAddress((String) map.get("stu_address"));
		stu.setIdCard((String) map.get("stu_idcard"));
		stu.setQq((String) map.get("stu_qq"));
		stu.setFatherName((String) map.get("stu_fathername"));
		stu.setFatherTel((String) map.get("stu_fathertel"));
		stu.setMotherName((String) map.get("stu_mothername"));
		stu.setMotherTel((String) map.get("stu_mothertel"));
		stu.setCreator((String) map.get("stu_creator"));
		if (map.get("stu_grade") != null) {
			stu.setGradeId(Integer.parseInt(map.get("stu_grade").toString()));
		}
		stu.setStatus((String) map.get("stu_status"));

		stu.setCreateTime((Date) map.get("stu_createtime"));
		stu.setIsAllocation((String) map.get("is_allocation"));
		if (map.get("counselor_id") != null) {
			stu.setCounselorId(Integer.parseInt(map.get("counselor_id").toString()));
		}
		if (map.get("grade_id") != null) {
			stu.setGradeId(Integer.parseInt(map.get("grade_id").toString()));
		}
		if (map.get("is_allpay") != null) {
			stu.setIs_allpay(Integer.parseInt(map.get("is_allpay").toString()));
		}
		if (map.get("class_type") != null) {
			stu.setClassType(Integer.parseInt(map.get("class_type").toString()));
		}
		if (map.get("class_type") != null) {
			stu.setClassType(Integer.parseInt(map.get("class_type").toString()));
		}
		if (map.get("class_id") != null) {
			stu.setClassId(Integer.parseInt(map.get("class_id").toString()));
		}

		return stu;
	}

	/**
	 * 取到集合之后再进行分页
	 * 
	 * @param request
	 * @param list
	 * @param pageSize
	 * @param page
	 * @return
	 */
	public List<Student> getListThenDividPage(HttpServletRequest request, List<Student> list, int page) {
		List<Student> subList = null;
		Integer totalCount = list.size();
		/* 计算出总共能分成多少页 */
		int pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
		if (totalCount > 0) {
			if (page <= pageCount) {
				// 当前页数为第一页
				if (page == 1) {
					// 数据总数小于每页显示的数据条数
					if (totalCount <= pageSize) {
						// 截止到总的数据条数(当前数据不足一页，按一页显示)，这样才不会出现数组越界异常
						subList = list.subList(0, totalCount);
					} else {
						subList = list.subList(0, pageSize);
					}
				} else {
					// 截取起始下标
					int fromIndex = (page - 1) * pageSize;
					// 截取截止下标
					int toIndex = page * pageSize;
					/* 计算截取截止下标 */
					if ((totalCount - toIndex) % pageSize >= 0) {
						toIndex = page * pageSize;
					} else {
						toIndex = (page - 1) * pageSize + (totalCount % pageSize);
					}
					if (totalCount >= toIndex) {
						subList = list.subList(fromIndex, toIndex);
					}
				}
			} else {
				subList = null;
			}
		}
		request.setAttribute("totalPage", pageCount);
		return subList;

	}
}
